MYDB=$DB_RENTAL_AGENCIES
INPUT_FOLDER=../data/raw_data/car_rental_agencies
OUTPUT_FOLDER=../data/data_csv

##### Read input #####
importCSVFile $MYDB $INPUT_FOLDER/car_rental_agencies.csv agencies
sqlite3 $MYDB "delete from agencies where CodeInsee = '';"

##### Calculate aggregates #####
# Aggregate by CodeInsee
calcAggregate $MYDB 1 tmp agencies COUNT CodeInsee

# Merge with communes
equalJoin $MYDB 1 tmp2 tmp mycommunes CodeInsee

# Aggregate to canton level
sqlite3 $MYDB "create table tmp_CantonId as \
    select CantonId, SUM(mycount) as NumCarRentalAgencies \
    from tmp2 group by CantonId;"

sqlite3 $MYDB "create table CarRentalAgencies_CantonId as \
    select a.CantonId, NumCarRentalAgencies \
    from Scope_CantonId a left join tmp_CantonId b on a.CantonId = b.CantonId"

sqlite3 $MYDB "update CarRentalAgencies_CantonId set NumCarRentalAgencies=0 where NumCarRentalAgencies is null; "
    
dropTables $MYDB tmp_CantonId

### Clean up
dropTables $MYDB tmp tmp2 agencies
dropTables $MYDB Scope_CantonId mycommunes
sqlite3 $MYDB "VACUUM;"
